package com.yulian.web_api.report;

import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;
import com.yulian.util.DataUtils;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.auth.User;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于业务逻辑层，主要实现运输管理收入单数据查询，更新数据，为MainVerticle中的REST接口提供服务
 *
 * @author
 */
public class TransportRevenueManager {
    final InternalLogger logger = Log4JLoggerFactory.getInstance(TransportRevenueManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("driver_name", "plate_number");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            logger.info("验证必须传递的字段: {}", must.toString());
            promise.complete(must);
            return promise.future();
        }
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("legal_entity_id", "goods_gather_area_id", "site_id", "freight_amount", "transport_management_amount", "mainline_transport_id", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表mainline_freight_payment的说明
        String legal_entity_id = bodyJSObj.getString("legal_entity_id");
        String goods_gather_area_id = bodyJSObj.getString("goods_gather_area_id");
        String site_id = bodyJSObj.getString("site_id");
        double freight_amount = Double.parseDouble(bodyJSObj.getString("freight_amount"));
        double transport_management_amount = Double.parseDouble(bodyJSObj.getString("transport_management_amount"));
        String mainline_transport_id = bodyJSObj.getString("mainline_transport_id");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("mainline_transport_id", mainline_transport_id);

        this.commonRepository.getCon(client).onSuccess(conn -> {
            String sql = createSelectSql(queryJSObj, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, sql, "getTransportRevenueRecords")
            .onSuccess(resJSArray -> { //resJSArray中就是获取到符合条件的数据
                //获取满足条件的记录总数
                String count_sql = this.commonRepository.createCountSql(queryJSObj, "mainline_freight_payment");
                Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_mainline_freight_payment");
                //获取运输商的编码和名称
                String legal_entity_sql = "select distinct legal_entity_name from  payment_entity where id = $1 and is_delete = 0";
                Future<String> getLegalEntityFuture = this.commonRepository.getCodeFuture2(conn, legal_entity_sql, Tuple.of(Long.parseLong(legal_entity_id)), "get_legal_entity_name", "供应商名");
                //获取供应链商品聚集区的编码和名称
                String goods_gather_area_sql = "select distinct goods_gather_area_name from  goods_gather_area where id = $1 and is_delete = 0";
                Future<String> getGoodsGatherAreaNameFuture = this.commonRepository.getCodeFuture2(conn, goods_gather_area_sql, Tuple.of(Long.parseLong(goods_gather_area_id)), "get_goods_gather_area_name", "聚集区名");
                //获取比价销售地编码和名称
                String site_sql = "select distinct  site_name from  site where id = $1 and is_delete = 0";
                Future<String> getSiteFuture = this.commonRepository.getCodeFuture2(conn, site_sql, Tuple.of(Long.parseLong(site_id)), "get_site_name", "发货日期");
                //发货日期
                String delivery_date_sql = "select distinct create_time from  batch_pickup_plan_load_completion where id = " +
                "(select distinct id from supplier_pickup_plan where prepare_pickup_plan_id = $1 and is_delete = 0) and is_delete = 0";
                Future<String> getDeliveryDataFuture = this.commonRepository.getCodeFuture2(conn, delivery_date_sql, Tuple.of(Long.parseLong(mainline_transport_id)), "get_delivery_date", "发货日期");
                //到货日期
                String arrival_date_sql = "select distinct arrival_date from  supplier_pickup_goods_inspect_accept where supplier_pickup_plan_id =" +
                "(select distinct id from supplier_pickup_plan where prepare_pickup_plan_id = " +
                "(select distinct prepare_pickup_plan_id from batch_pickup_plan where id = " +
                "(select distinct batch_pickup_plan_id from batch_pickup_plan_load_completion where id = " +
                "(select distinct batch_pickup_plan_load_completion_id from mainline_transport where id = $1 and is_delete = 0)and is_delete = 0)and is_delete = 0)and is_delete = 0) and is_delete = 0";
                Future<String> getArrivalDataFuture = this.commonRepository.getCodeFuture2(conn, arrival_date_sql, Tuple.of(Long.parseLong(mainline_transport_id)), "get_site_name", "获取销售地区");


                CompositeFuture.join(Arrays.asList(getTotalFuture, getLegalEntityFuture, getGoodsGatherAreaNameFuture, getSiteFuture, getDeliveryDataFuture, getArrivalDataFuture)).onComplete(ar2 -> {
                    if (ar2.succeeded()) {
                        int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                        String legal_entity_name = (String) ar2.result().list().get(1);
                        String goods_gather_area_name = (String) ar2.result().list().get(2);
                        String site_name = (String) ar2.result().list().get(3);
                        String delivery_date = (String) ar2.result().list().get(4);
                        String arrival_date = (String) ar2.result().list().get(5);
                        // 最后组装含有name的Json对象和数组
                        JsonArray recordsJSArray = new JsonArray();
                        for (int i = 0; i < resJSArray.size(); i++) {
                            JsonObject record_obj = resJSArray.getJsonObject(i);
                            record_obj.put("legal_entity_id", legal_entity_id);
                            record_obj.put("legal_entity_name", legal_entity_name);
                            record_obj.put("goods_gather_area_id", goods_gather_area_id);
                            record_obj.put("goods_gather_area_name", goods_gather_area_name);
                            record_obj.put("site_id", site_id);
                            record_obj.put("site_name", site_name);
                            record_obj.put("delivery_date", delivery_date);
                            record_obj.put("arrival_date", arrival_date);
                            record_obj.put("freight_amount", freight_amount);
                            record_obj.put("transport_management_amount", transport_management_amount);
                            record_obj.put("payment_acount", transport_management_amount + freight_amount);
                            recordsJSArray.add(record_obj);
                        }
                        JsonObject dataJSObj = new JsonObject();
                        dataJSObj.put("total", total_records).put("records", recordsJSArray);
                        responseJSObj.put("id", 0).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    } else {
                        logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                        promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getTransportRevenueRecords", "获取运输管理收入单基础参数用失败"));
                    }
                });
            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getTransportRevenueRecords", "获取运输管理收入单基础参数用失败")));
        }).
        onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getTransportRevenueRecords", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("transportation_revenue_code_code"), new CustomSql("pay_account_number"), new CustomSql("pay_account_bank"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("transport_revenue");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 该方法实现接收web前端通过body传入的参数，调用数据访问层CommonParameterRepositoryImpl 类的方法实现数据的增、删、改。
     * 通过含参数的Json对象bodyJSObj，通过method字段获取操作类型，分别是insert，update, delete
     * 1) 操作类型为 insert，新增数据
     * 2）操作类型为 update，更新数据
     * 3）操作类型为 delete，逻辑删除多条记录
     * 调用TransportRevenueerRepositoryImpl 的方法
     *
     * @param client
     * @param bodyJSObj
     * @param user
     * @return
     */
    public Future<JsonObject> insertOrUpdate(PgPool client, JsonObject bodyJSObj, User user) {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        String create_user = user.attributes().getJsonObject("accessToken").getString("username");
        //最后返回的json对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的非数值字段
        List<String> must_fields = Arrays.asList("method", "pay_account_number", "pay_account_bank");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        // 验证必须传递的数字字段
        List<String> numeric_fields = Arrays.asList("id", "legal_entity_id");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //获取bodyJSObj参数对象中字段的值,各字段注释请参看数据库设计文档中数据表mainline_freight_payment的说明
        //获取操作类型字段的值
        String method = bodyJSObj.getString("method");
        logger.info("method {}", method);
        Long legal_entity_id = Long.parseLong(bodyJSObj.getString("legal_entity_id"));
        String pay_account_number = bodyJSObj.getString("pay_account_number");
        String pay_account_bank = bodyJSObj.getString("pay_account_bank");

        // 获取bodyJSObj参数对象中字段的值
        long id = Long.valueOf(bodyJSObj.getString("id"));
        //验证字段是否为空
        if (DataUtils.isEmpty(method, pay_account_number, pay_account_bank, legal_entity_id)) {
            promise.fail(CommonUtils.createJsonObject(408, "参数 不合法"));
            return promise.future();
        }
        this.commonRepository.getCon(client).onSuccess(conn -> {
            /**
             * 校验多个id是否合法
             */
            String legal_entity_id_sql = "select id, legal_entity_name from payment_entity where id = $1 and is_delete = 0 limit 1";
            Future<JsonObject> legalEntityCodeFuture = this.commonRepository.queryOneJsonObjectThrow(conn, legal_entity_id_sql, Tuple.of(legal_entity_id), "update_mainline_freight_payment", "付款账号主体 操作失败");

            if (method.equals("update")) {
                // 并行执行多个future
                CompositeFuture.join(Arrays.asList(legalEntityCodeFuture))
                .onComplete(ar -> {
                    if (ar.succeeded()) {
                        String update_sql = "UPDATE transport_revenue SET pay_account_number= $1,pay_account_bank= $2,legal_entity_id= $3,update_user = $4 WHERE id = $5 RETURNING id";
                        this.commonRepository.insertOrUpdate(conn, update_sql, Tuple.of(pay_account_number, pay_account_bank, legal_entity_id, create_user, id), "update_transport_revenue")
                        .onSuccess(rows2 -> {
                            if (rows2.rowCount() > 0) {
                                //取出更新记录的id
                                long[] temp_id = new long[1];
                                rows2.forEach(item -> {
                                    temp_id[0] = item.getLong("id");
                                });
                                responseJSObj.put("id", 0).put("msg", "更新运输管理收入单成功,更新记录id: " + temp_id[0]);
                            } else {  //更新数据返回结果行数为0
                                responseJSObj.put("id", -1).put("msg", "没有更新运输管理收入单");
                            }
                            promise.complete(responseJSObj);
                            //写入操作日志
                            this.commonRepository.writePlatformLog(conn, user_id, "/web/transport_revenue/update", bodyJSObj, responseJSObj, "")
                            .onComplete(ar3 -> {
                                // promise.complete(responseJSObj);
                            });
                        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "updateTransportRevenueer", "更新运输管理收入单失败")));

                    } else {
                        promise.fail(CommonUtils.createExceptionJsonObjectStr(ar, "updateTransportRevenueer", "更新数据发生错误"));
                    }
                });
            }
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "getTransportRevenueerRecords", "数据库连接失败")));
        return promise.future();
    }
}
